using System;
using System.Collections;
using System.Data;
using System.Web;
using BEGDC.CC.BL.CCFlow.CR;
using BEGDC.CC.BL.Common;
using BEGDC.CC.BL.Common.Query;
using BEGDC.CC.Entity;
using BEGDC.Utility.Castle;
using NHibernate;

namespace BEGDC.CC.ExcelTool
{
	/// <summary>
	/// Summary description for ExportEBDFormA.
	/// </summary>
	public class ExportEBDFormA:ICCExcelExport
	{
		private string templateName = HttpContext.Current.Server.MapPath(System.Configuration.ConfigurationSettings.AppSettings["EBDFormATemplate"]);
		private string targetFileName = HttpContext.Current.Server.MapPath(System.Configuration.ConfigurationSettings.AppSettings["ExportPath"]);
		private string fileName;

		private IExcelExport excelExport;

		public ExportEBDFormA()
		{
			this.excelExport = new ExcelExportCommom();
		}
		public ExportEBDFormA(IExcelExport excelExport)
		{
			this.excelExport = excelExport;
		}

		private void GenerateFileName()
		{
			DateTime tmpTime=DateTime.Now;
			this.fileName+=tmpTime.Millisecond.ToString();
			this.fileName+=tmpTime.Second.ToString();
			this.fileName+=tmpTime.Minute.ToString();
			this.fileName+="_EBDFormA.xls";
		}

		public void GenerateEBDFormA(int appId )
		{
			try
			{
				this.excelExport.Open( templateName );

				BEGDC.CC.BL.Common.Query.IQuery queryService =  AppContainer.Instance[ServiceConst.QueryService] as BEGDC.CC.BL.Common.Query.IQuery;
								
				//Fill in application information
				//including AppNo
				//RequsterInfo--PART1,  Someelse PART2, RequestedDate info - PART7 Visit Site - PART4
				GenerateAppInfo(queryService, appId );
			
				//Fill in customer information PART3, PART5
				GenerateCustomerInfo (queryService, appId );

				//Fill Business purpose info PART6
				GenerateBusinessPurpose( queryService, appId );
			
				//Fill Topic info PART8
				GenerateTopic( queryService, appId );

				//Fill CustomerAttendee PART9
				GenerateCustomerAttendee( queryService, appId );
			
				//Fill ASBAttendee PART10
				GenerateASBAttendee( queryService, appId );

				//Fill Sales Team Info PART11
				GenerateSalesTeamInfo( queryService, appId );

				//Fill Logistical Info PART12, PART14
				GenerateLogisticalInfo( queryService, appId );

				//Fill Payment Info PART13
				GeneratePaymentInfo( queryService, appId );

				this.fileName=this.fileName.Replace("/","").Replace("\\","").Replace("'","").Replace("\"","");
				this.fileName=this.targetFileName + this.fileName;

				this.excelExport.ExportAs(this.fileName);
			}
			catch(Exception ex)
			{
				BEGDC.Utility.ExceptionManagement.GDCException exp=new BEGDC.Utility.ExceptionManagement.GDCException("export_0001",ex.Message,ex.InnerException);
				throw exp;
			}
			finally
			{
				this.excelExport.Close();
			}
		}
		
		private void GenerateAppInfo(BEGDC.CC.BL.Common.Query.IQuery queryService, int appId )
		{
			Application app = queryService.GetAppById( appId );
		
			if( app == null )
				return;
			
			BEGDC.CC.BL.Common.IUtility utility = AppContainer.Instance[ServiceConst.UtilityService] as BEGDC.CC.BL.Common.IUtility;
			IList list;
			//set AppNo
			string[] strList;
			Excel.Range rngTemp = this.excelExport.MyApp.get_Range("AppNo", Type.Missing );
			this.excelExport.MyApp.get_Range( rngTemp.Cells[1,1], rngTemp.Cells[1,1]).Value2 = app.AppNo;
			this.fileName =app.AppNo + "_" + this.fileName;
			
			#region set Requestor information

			rngTemp = this.excelExport.MyApp.get_Range("RequestorInfo", Type.Missing );

			if (app.RequesterNameEN==string.Empty || app.RequesterNameEN=="" || app.RequesterNameEN==null)
				this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = app.RequesterNameCN;
			else
				this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = app.RequesterNameEN;

			this.excelExport.MyApp.get_Range( rngTemp.Cells[2,3], rngTemp.Cells[2,3]).Value2 = app.RequesterOfficeTel;
			this.excelExport.MyApp.get_Range( rngTemp.Cells[3,3], rngTemp.Cells[3,3]).Value2 = app.RequesterMobile;
			//Add by belinda 20070806.
			if(app.IsSSME)
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[4,3], rngTemp.Cells[4,3]).Value2 = "SSME";
				this.excelExport.MyApp.get_Range( rngTemp.Cells[5,3], rngTemp.Cells[5,3]).Value2 = app.CostCenterCode.DicItemKey;
			}
			else
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[4,3], rngTemp.Cells[4,3]).Value2 = "Not SSME";
				this.excelExport.MyApp.get_Range( rngTemp.Cells[6,3], rngTemp.Cells[6,3]).Value2 = app.DepartmentType.DicItemKey;
			}
			//end.
			if (app.LegalEntity != null)
				this.excelExport.MyApp.get_Range( rngTemp.Cells[7,3], rngTemp.Cells[7,3]).Value2 = app.LegalEntity.LegalEntityName;

			if (app.ApplyForSomeoneElse)
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[8,3], rngTemp.Cells[8,3]).Value2 = "Yes";
				//set Someone else info
				rngTemp = this.excelExport.MyApp.get_Range("SomeoneElse", Type.Missing );
				this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = app.SomeoneElseHRID;
				this.excelExport.MyApp.get_Range( rngTemp.Cells[2,3], rngTemp.Cells[2,3]).Value2 = app.SomeoneElseNameEN;
				this.excelExport.MyApp.get_Range( rngTemp.Cells[3,3], rngTemp.Cells[3,3]).Value2 = app.SomeoneElseTel;
				this.excelExport.MyApp.get_Range( rngTemp.Cells[4,3], rngTemp.Cells[4,3]).Value2 = app.SomeoneElseMobil;
				this.excelExport.MyApp.get_Range( rngTemp.Cells[5,3], rngTemp.Cells[5,3]).Value2 = app.SomeoneElseEmail;
			}
			else
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[8,3], rngTemp.Cells[8,3]).Value2 = "No";
			}
			#endregion

			#region set Request Date -- Part 7
			//PART7 Request Date
			rngTemp = this.excelExport.MyApp.get_Range("DateInfo", Type.Missing );
			if (app.RequestedDateFrom != Nullables.NullableDateTime.Default)
			{
				if (app.RequestedDateTo != Nullables.NullableDateTime.Default)
					this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = ((System.DateTime)app.RequestedDateFrom.Value).ToString("MM/dd/yyyy") + " - " + ((System.DateTime)app.RequestedDateTo.Value).ToString("MM/dd/yyyy");
				else
					this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = ((System.DateTime)app.RequestedDateFrom.Value).ToString("MM/dd/yyyy") ;
			}
			else
			{
				if (app.RequestedDateTo != Nullables.NullableDateTime.Default)
					this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = " - " + ((System.DateTime)app.RequestedDateTo.Value).ToString("MM/dd/yyyy");
			}
//			//PART7 Alternate Date  ---Add by belinda 20070807
//			if (app.AlternateDateFrom != Nullables.NullableDateTime.Default)
//			{
//				if (app.AlternateDateTo != Nullables.NullableDateTime.Default)
//					this.excelExport.MyApp.get_Range(rngTemp.Cells[2, 3], rngTemp.Cells[2, 3]).Value2 = ((System.DateTime)app.AlternateDateFrom.Value).ToString("MM/dd/yyyy") + " - " + ((System.DateTime)app.AlternateDateTo.Value).ToString("MM/dd/yyyy");
//				else
//					this.excelExport.MyApp.get_Range(rngTemp.Cells[2, 3], rngTemp.Cells[2, 3]).Value2 = ((System.DateTime)app.AlternateDateFrom.Value).ToString("MM/dd/yyyy");
//			}
//			else
//			{
//				if (app.AlternateDateTo != Nullables.NullableDateTime.Default)
//					this.excelExport.MyApp.get_Range(rngTemp.Cells[2, 3], rngTemp.Cells[2, 3]).Value2 = " - " + ((System.DateTime)app.AlternateDateTo.Value).ToString("MM/dd/yyyy");
//			}
			#endregion

			#region preRequest review guide, other info in customerinfo
			rngTemp = this.excelExport.MyApp.get_Range("PreRequestInfo", Type.Missing );			
			if (app.IsReviewedGuideline)
				this.excelExport.MyApp.get_Range( rngTemp.Cells[4,3], rngTemp.Cells[4,3]).Value2 = "Yes";
			else
				this.excelExport.MyApp.get_Range( rngTemp.Cells[4,3], rngTemp.Cells[4,3]).Value2 = "No";
			#endregion

			#region set visit site info
			rngTemp = this.excelExport.MyApp.get_Range("VisitSiteInfo", Type.Missing );
			
			DataTable dtSite = queryService.GetVisitingSite(appId);
			if (dtSite.Rows.Count >0)
			{
				list = utility.BuildDicList( BEGDC.CC.BL.Common.DicType.OCOVisitingSites);
				for (int i=0;i<dtSite.Rows.Count;i++)
				{
					for (int j=0;j<list.Count;j++)
					{
						if (dtSite.Rows[i]["DicItemID"].ToString()==((DicItem)list[j]).Id.ToString())
						{
							this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 += (i+1).ToString() + ") " + ((DicItem)list[j]).DicItemKey + "\n";				
							break;
						}
					}
				}

			}
			this.excelExport.MyApp.get_Range( rngTemp.Cells[2,3], rngTemp.Cells[2,3]).Value2=app.OtherSite;
			#endregion
		}
		
		private string[] SplitList(string strToSplit)
		{
			string[] temp;
			temp=strToSplit.Split(',');
			return temp;
		}

		private void GenerateCustomerAttendee(BEGDC.CC.BL.Common.Query.IQuery queryService, int appId )
		{
			DataTable custAttendee = queryService.QueryCustomerAttendeeByAppId( appId );
			Excel.Range rngTemp = this.excelExport.MyApp.get_Range("CustomerAttendee", Type.Missing);
			int rowCount=2;
			while (custAttendee.Rows.Count> rowCount)
			{
				this.excelExport.MyApp.get_Range(rngTemp.Cells[2,1],rngTemp.Cells[2,7]).EntireRow.Copy(Type.Missing);
				this.excelExport.MyApp.get_Range(rngTemp.Cells[2,1],rngTemp.Cells[2,7]).EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);
				rowCount++;
			}

			rngTemp = this.excelExport.MyApp.get_Range("CustomerAttendee", Type.Missing);

			if( custAttendee != null && custAttendee.Rows.Count > 0 )
			{
				for( int i=0; i<custAttendee.Rows.Count; i++)
				{
					//prefer NameEN
					if( custAttendee.Rows[i]["NameEN"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,1], rngTemp.Cells[i+1,1]).Value2 = custAttendee.Rows[i]["NameEN"].ToString();

					if( custAttendee.Rows[i]["CompanyEN"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,3], rngTemp.Cells[i+1,3]).Value2 = custAttendee.Rows[i]["CompanyEN"].ToString();
				
					if( custAttendee.Rows[i]["TitleEN"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,4], rngTemp.Cells[i+1,4]).Value2 = custAttendee.Rows[i]["TitleEN"].ToString();

					if( custAttendee.Rows[i]["Responsibility"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,5], rngTemp.Cells[i+1,5]).Value2 = custAttendee.Rows[i]["Responsibility"].ToString();
				
					if( custAttendee.Rows[i]["Affects"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,6], rngTemp.Cells[i+1,6]).Value2 = custAttendee.Rows[i]["Affects"].ToString();

					if( custAttendee.Rows[i]["Remark"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,7], rngTemp.Cells[i+1,7]).Value2 = custAttendee.Rows[i]["Remark"].ToString();
					}
			}
		}

		private void GenerateASBAttendee( BEGDC.CC.BL.Common.Query.IQuery queryService, int appId)
		{
			DataTable alAttendee = queryService.QueryALAttendeeByAppId( appId );
			Excel.Range rngTemp = this.excelExport.MyApp.get_Range("ALAttendee", Type.Missing);
			int rowCount=2;
			while (alAttendee.Rows.Count> rowCount)
			{
				this.excelExport.MyApp.get_Range(rngTemp.Cells[2,1],rngTemp.Cells[2,7]).EntireRow.Copy(Type.Missing);
				this.excelExport.MyApp.get_Range(rngTemp.Cells[2,1],rngTemp.Cells[2,7]).EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);
				rowCount++;
			}

			rngTemp = this.excelExport.MyApp.get_Range("ALAttendee", Type.Missing);

			if( alAttendee != null && alAttendee.Rows.Count > 0 )
			{
				for( int i=0; i<alAttendee.Rows.Count; i++)
				{
					if( alAttendee.Rows[i]["NameEN"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,1], rngTemp.Cells[i+1,1]).Value2 = alAttendee.Rows[i]["NameEN"].ToString();
					if( alAttendee.Rows[i]["Title"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,3], rngTemp.Cells[i+1,3]).Value2 = alAttendee.Rows[i]["Title"].ToString();					
				}
			}
		}

		private void GenerateCustomerInfo(BEGDC.CC.BL.Common.Query.IQuery queryService, int appId)
		{
			BEGDC.CC.Entity.CustomerInfo custInfo=queryService.GetCustomerInfo(appId);
			if (custInfo==null)
				return;
		
			BEGDC.CC.BL.Common.IUtility utility = AppContainer.Instance[ServiceConst.UtilityService] as BEGDC.CC.BL.Common.IUtility;
			IList list;
		
			//set customer info
			Excel.Range rngTemp = this.excelExport.MyApp.get_Range("CustomerInfo", Type.Missing );
			this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = custInfo.NumberOfPerson;
			this.excelExport.MyApp.get_Range( rngTemp.Cells[2,3], rngTemp.Cells[2,3]).Value2 = custInfo.VisitObjective;
			this.excelExport.MyApp.get_Range( rngTemp.Cells[3,3], rngTemp.Cells[3,3]).Value2 = custInfo.CompanyProfile;
			this.excelExport.MyApp.get_Range( rngTemp.Cells[4,3], rngTemp.Cells[4,3]).Value2 = custInfo.AccountType.Description;

			//set pre-request info
			rngTemp = this.excelExport.MyApp.get_Range("PreRequestInfo", Type.Missing );
			this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = custInfo.Company;
			if (custInfo.IsFCPA)
				this.excelExport.MyApp.get_Range( rngTemp.Cells[2,3], rngTemp.Cells[2,3]).Value2 = "Yes";
			else
				this.excelExport.MyApp.get_Range( rngTemp.Cells[2,3], rngTemp.Cells[2,3]).Value2 = "No";
			this.excelExport.MyApp.get_Range( rngTemp.Cells[3,3], rngTemp.Cells[3,3]).Value2 = custInfo.Country;

			//is review guide line is in app table
		}

		private void GenerateBusinessPurpose(BEGDC.CC.BL.Common.Query.IQuery queryService, int appId)
		{
			BEGDC.CC.Entity.BusinessPurpose busPurpose=queryService.GetBusinessPurposeInfo(appId);
			if (busPurpose==null)
				return;
			
			//set Business Purpose info
			Excel.Range rngTemp = this.excelExport.MyApp.get_Range("BusinessPurpose", Type.Missing );
			if (busPurpose.IsInSalesContract)
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 ="Yes";
				if (busPurpose.IsContractSigned)
				{
					this.excelExport.MyApp.get_Range( rngTemp.Cells[3,3], rngTemp.Cells[3,3]).Value2 ="Yes";
				}
				else
				{
					this.excelExport.MyApp.get_Range( rngTemp.Cells[3,3], rngTemp.Cells[3,3]).Value2 ="No";
				}
			}
			else
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 ="No";
				this.excelExport.MyApp.get_Range( rngTemp.Cells[2,3], rngTemp.Cells[2,3]).Value2 =busPurpose.Opportunities;

			}

			#region Contract info
			int rowCount=2;
			double totalPrice=0;
			rngTemp = this.excelExport.MyApp.get_Range("ContractInfo", Type.Missing );
			DataTable dtContract=queryService.QueryContractInfoByAppId(appId);
			if (dtContract.Rows.Count>0)
			{
				
				while (dtContract.Rows.Count > rowCount)
				{
					this.excelExport.MyApp.get_Range(rngTemp.Cells[2,1],rngTemp.Cells[2,7]).EntireRow.Copy(Type.Missing);
					this.excelExport.MyApp.get_Range(rngTemp.Cells[2,1],rngTemp.Cells[2,7]).EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);
					rowCount++;
				}

				rngTemp = this.excelExport.MyApp.get_Range("ContractInfo", Type.Missing );		
				for( int i=0; i < dtContract.Rows.Count; i++)
				{
					if( dtContract.Rows[i]["DateOfContract"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,1], rngTemp.Cells[i+1,1]).Value2=((System.DateTime)dtContract.Rows[i]["DateOfContract"]).ToString("yyyy-MM-dd");
					if( dtContract.Rows[i]["ContractNo"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,2], rngTemp.Cells[i+1,2]).Value2=dtContract.Rows[i]["ContractNo"].ToString();

					if( dtContract.Rows[i]["ProducstSold"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,3], rngTemp.Cells[i+1,3]).Value2=dtContract.Rows[i]["ProducstSold"].ToString();
					if( dtContract.Rows[i]["VisitPurposeInContract"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,4], rngTemp.Cells[i+1,4]).Value2=dtContract.Rows[i]["VisitPurposeInContract"].ToString();
					if( dtContract.Rows[i]["Quotas"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,5], rngTemp.Cells[i+1,5]).Value2=dtContract.Rows[i]["Quotas"].ToString();
					
					if( dtContract.Rows[i]["UnitPrice"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,6], rngTemp.Cells[i+1,6]).Value2=dtContract.Rows[i]["UnitPrice"].ToString();
					
					if( dtContract.Rows[i]["Budget"] != System.DBNull.Value )
					{
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,7], rngTemp.Cells[i+1,7]).Value2=dtContract.Rows[i]["Budget"].ToString();
						totalPrice+=Convert.ToDouble(dtContract.Rows[i]["Budget"].ToString());
					}
				}
			}
			#endregion
			
			rowCount++;
			this.excelExport.MyApp.get_Range(rngTemp.Cells[rowCount,7], rngTemp.Cells[rowCount,7]).Value2 = totalPrice;

			
			if (busPurpose.RecreationalActivity)
			{
				this.excelExport.MyApp.get_Range(rngTemp.Cells[rowCount+1,3], rngTemp.Cells[rowCount+1,3]).Value2 = "Yes";
				//this.excelExport.MyApp.get_Range(rngTemp.Cells[rowCount+2,3], rngTemp.Cells[rowCount+2,3]).Value2 = busPurpose.Itinerary;
			}
			else
			{
				this.excelExport.MyApp.get_Range(rngTemp.Cells[rowCount+1,3], rngTemp.Cells[rowCount+1,3]).Value2 = "No";
			}
		}

		private void GenerateTopic(BEGDC.CC.BL.Common.Query.IQuery queryService, int appId)
		{
			BEGDC.CC.Entity.Topic topic=queryService.GetTopicInfo(appId);
			if (topic==null)
				return;
			
			//set Topic info
			Excel.Range rngTemp = this.excelExport.MyApp.get_Range("Topic", Type.Missing );
			this.excelExport.MyApp.get_Range( rngTemp.Cells[1,1], rngTemp.Cells[1,1]).Value2 = topic.TopicName;
		}

		private void GenerateSalesTeamInfo(BEGDC.CC.BL.Common.Query.IQuery queryService, int appId)
		{
			BEGDC.CC.Entity.SalesTeamInfo salesTeam=queryService.GetSalesTeamInfo(appId);
			if (salesTeam==null)
				return;
			BEGDC.CC.BL.Common.IUtility utility = AppContainer.Instance[ServiceConst.UtilityService] as BEGDC.CC.BL.Common.IUtility;
			IList list;

			Excel.Range rngTemp = this.excelExport.MyApp.get_Range("SalesTeam", Type.Missing );
			this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = salesTeam.HostNameEN;
			this.excelExport.MyApp.get_Range( rngTemp.Cells[2,3], rngTemp.Cells[2,3]).Value2 = salesTeam.OfficeTel;
			this.excelExport.MyApp.get_Range( rngTemp.Cells[3,3], rngTemp.Cells[3,3]).Value2 = salesTeam.OfficeFax;
			this.excelExport.MyApp.get_Range( rngTemp.Cells[4,3], rngTemp.Cells[4,3]).Value2 = salesTeam.Email;

			list = utility.BuildDicList( BEGDC.CC.BL.Common.DicType.SALES_BUSINESS_UNIT_OCO);
			for (int i=0;i<list.Count;i++)
			{
				if (((DicItem)list[i]).Id.ToString() == salesTeam.SalesBusinessUnit)
				{
					this.excelExport.MyApp.get_Range( rngTemp.Cells[5,3], rngTemp.Cells[5,3]).Value2=((DicItem)list[i]).DicItemKey;
					break;					
				}
			}
			//this.excelExport.MyApp.get_Range( rngTemp.Cells[5,3], rngTemp.Cells[5,3]).Value2 = salesTeam.SalesBusinessUnit;

			list = utility.BuildDicList( BEGDC.CC.BL.Common.DicType.REGION_OCO);
			for (int i=0;i<list.Count;i++)
			{
				if (((DicItem)list[i]).Id.ToString() == salesTeam.Region)
				{
					this.excelExport.MyApp.get_Range( rngTemp.Cells[6,3], rngTemp.Cells[6,3]).Value2=((DicItem)list[i]).DicItemKey;
					break;					
				}
			}
			//this.excelExport.MyApp.get_Range( rngTemp.Cells[6,3], rngTemp.Cells[6,3]).Value2 = salesTeam.Region;
		}

		private void GenerateLogisticalInfo(BEGDC.CC.BL.Common.Query.IQuery queryService, int appId)
		{
			BEGDC.CC.Entity.LogisticalInfo lgsInfo=queryService.GetLogisticalInfo(appId);
			if (lgsInfo==null)
				return;
			
			int rowCount;
			ICR crService = AppContainer.Instance[ServiceConst.CRService] as ICR;

			#region Hotel info
			Excel.Range rngTemp = this.excelExport.MyApp.get_Range("NeedHotel", Type.Missing );
			if(lgsInfo.IsNeedHotel)
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = "Yes";
				if(lgsInfo.PaymentForHotel != null )
					this.excelExport.MyApp.get_Range( rngTemp.Cells[2,3], rngTemp.Cells[2,3]).Value2 = lgsInfo.PaymentForHotel.DicItemKey;
				
				rngTemp = this.excelExport.MyApp.get_Range("HotelInfo", Type.Missing );
				rowCount=2;
				DataTable dtHotel=crService.GetHotelRoomBookingList(lgsInfo.Id,false);

				while (dtHotel.Rows.Count > rowCount)
				{
					this.excelExport.MyApp.get_Range(rngTemp.Cells[2,1],rngTemp.Cells[2,7]).EntireRow.Copy(Type.Missing);
					this.excelExport.MyApp.get_Range(rngTemp.Cells[2,1],rngTemp.Cells[2,7]).EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);
					rowCount++;
				}
			
				for(int i=0;i<dtHotel.Rows.Count;i++)
				{
					if (dtHotel.Rows[i]["HotelName"]!=System.DBNull.Value)
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,1], rngTemp.Cells[i+1,1]).Value2=dtHotel.Rows[i]["HotelName"];
					if (dtHotel.Rows[i]["RoomTypeName"]!=System.DBNull.Value)
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,3], rngTemp.Cells[i+1,3]).Value2=dtHotel.Rows[i]["RoomTypeName"];
					if (dtHotel.Rows[i]["RoomNumber"]!=System.DBNull.Value)
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,6], rngTemp.Cells[i+1,6]).Value2=dtHotel.Rows[i]["RoomNumber"];
				}
			}
			else
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = "No";
			}
			#endregion

			#region Vehicle Info

//			Decimal totalPrice=0;
			rngTemp = this.excelExport.MyApp.get_Range("NeedTrans", Type.Missing );

			if(lgsInfo.IsNeedGroundTransportation)
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = "Yes";
				
				#region ASB Vehicle Info
				rngTemp = this.excelExport.MyApp.get_Range("ASBVehicle", Type.Missing );
				DataTable dtVehicle=crService.GetVehicleBookingList(lgsInfo.Id,"Alcatel");
				rowCount=2;
				while (dtVehicle.Rows.Count>rowCount)
				{
					this.excelExport.MyApp.get_Range(rngTemp.Cells[2,1],rngTemp.Cells[2,7]).EntireRow.Copy(Type.Missing);
					this.excelExport.MyApp.get_Range(rngTemp.Cells[2,1],rngTemp.Cells[2,7]).EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);
					rowCount++;
				}
				for(int i=0;i<dtVehicle.Rows.Count;i++)
				{
					if (dtVehicle.Rows[i]["VehicleType"] != System.DBNull.Value)
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,1], rngTemp.Cells[i+1,1]).Value2=dtVehicle.Rows[i]["VehicleType"];
					if (dtVehicle.Rows[i]["VehicleNumber"] != System.DBNull.Value)
					{
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,3], rngTemp.Cells[i+1,3]).Value2=dtVehicle.Rows[i]["VehicleNumber"];
					}			
				}
				#endregion

				#region Lease vehicle info
				rngTemp = this.excelExport.MyApp.get_Range("VehicleInfos", Type.Missing );
				dtVehicle=crService.GetVehicleBookingList(lgsInfo.Id,"Lease");
				rowCount=2;				

				while (dtVehicle.Rows.Count>rowCount)
				{
					this.excelExport.MyApp.get_Range(rngTemp.Cells[2,1],rngTemp.Cells[2,7]).EntireRow.Copy(Type.Missing);
					this.excelExport.MyApp.get_Range(rngTemp.Cells[2,1],rngTemp.Cells[2,7]).EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);
					rowCount++;
				}
				for(int i=0;i<dtVehicle.Rows.Count;i++)
				{
					if (dtVehicle.Rows[i]["VehicleType"] != System.DBNull.Value)
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,1], rngTemp.Cells[i+1,1]).Value2=dtVehicle.Rows[i]["VehicleType"];
					if (dtVehicle.Rows[i]["VehicleNumber"] != System.DBNull.Value)
					{
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,3], rngTemp.Cells[i+1,3]).Value2=dtVehicle.Rows[i]["VehicleNumber"];
//						if (dtVehicle.Rows[i]["Price"] != System.DBNull.Value)
//						{
//							this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,6], rngTemp.Cells[i+1,6]).Value2=dtVehicle.Rows[i]["Price"];
//							totalPrice+=Convert.ToInt32(dtVehicle.Rows[i]["VehicleNumber"]) * Convert.ToDecimal(dtVehicle.Rows[i]["Price"]);
//						}
					}			
				}
				#endregion

//				rowCount++;
//				//Total Price
//				this.excelExport.MyApp.get_Range( rngTemp.Cells[rowCount,3], rngTemp.Cells[rowCount,3]).Value2=totalPrice;
				rowCount++;
				this.excelExport.MyApp.get_Range( rngTemp.Cells[rowCount,3], rngTemp.Cells[rowCount,3]).Value2=lgsInfo.Comment;
			}
			else
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = "No";
			}		
			
			#endregion

			rngTemp = this.excelExport.MyApp.get_Range("GiftInfo", Type.Missing );
			rowCount = 0;

			//Entertainment
			if (lgsInfo.IsNeedEntertainment)
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[rowCount+1,3], rngTemp.Cells[rowCount+1,3]).Value2 ="Yes";
				this.excelExport.MyApp.get_Range( rngTemp.Cells[rowCount+2,3], rngTemp.Cells[rowCount+2,3]).Value2 =lgsInfo.EntertainmentPreference;
				this.excelExport.MyApp.get_Range( rngTemp.Cells[rowCount+3,3], rngTemp.Cells[rowCount+3,3]).Value2 =lgsInfo.DecryptedEntertainmentPriceFrom.ToString() + " - " + lgsInfo.DecryptedEntertainmentPriceTo.ToString();
			}
			else
				this.excelExport.MyApp.get_Range( rngTemp.Cells[rowCount+1,3], rngTemp.Cells[rowCount+1,3]).Value2  ="No";
			
			//Gift
			if (lgsInfo.IsNeedGift)
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[rowCount+4,3], rngTemp.Cells[rowCount+4,3]).Value2 ="Yes";
				this.excelExport.MyApp.get_Range( rngTemp.Cells[rowCount+5,3], rngTemp.Cells[rowCount+5,3]).Value2 = lgsInfo.GiftDetails;
				this.excelExport.MyApp.get_Range( rngTemp.Cells[rowCount+6,3], rngTemp.Cells[rowCount+6,3]).Value2 = lgsInfo.DecryptedGiftPriceFrom.ToString() + " - " + lgsInfo.DecryptedGiftPriceTo.ToString();

			}
			else
				this.excelExport.MyApp.get_Range( rngTemp.Cells[rowCount+4,3], rngTemp.Cells[rowCount+4,3]).Value2 ="No";
			
			if (lgsInfo.IsReceivedALGiftBefore)
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[rowCount+7,3], rngTemp.Cells[rowCount+7,3]).Value2 ="Yes";
				this.excelExport.MyApp.get_Range( rngTemp.Cells[rowCount+8,3], rngTemp.Cells[rowCount+8,3]).Value2 =lgsInfo.ReceivedGiftDetails;
			}
			else
				this.excelExport.MyApp.get_Range( rngTemp.Cells[rowCount+7,3], rngTemp.Cells[rowCount+7,3]).Value2  ="No";

			//Travel
			if (lgsInfo.IsNeedTravelAgency)
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[rowCount+9,3], rngTemp.Cells[rowCount+9,3]).Value2 ="Yes";
//				this.excelExport.MyApp.get_Range( rngTemp.Cells[rowCount+10,3], rngTemp.Cells[rowCount+10,3]).Value2 =lgsInfo.TravelAgency;
			}
			else
				this.excelExport.MyApp.get_Range( rngTemp.Cells[rowCount+9,3], rngTemp.Cells[rowCount+9,3]).Value2  ="No";

			if (lgsInfo.IsNeedInterpreter)
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[rowCount+10,3], rngTemp.Cells[rowCount+10,3]).Value2 ="Yes";

				//look up
				if (lgsInfo.InterpretionType==1)
					this.excelExport.MyApp.get_Range( rngTemp.Cells[rowCount+11,3], rngTemp.Cells[rowCount+11,3]).Value2 ="Simultaneous";
				else
					this.excelExport.MyApp.get_Range( rngTemp.Cells[rowCount+11,3], rngTemp.Cells[rowCount+11,3]).Value2 ="Consecutive";
			}
			else
				this.excelExport.MyApp.get_Range( rngTemp.Cells[rowCount+10,3], rngTemp.Cells[rowCount+10,3]).Value2 ="No";

			if (lgsInfo.SpecialDialect !="" && lgsInfo.SpecialDialect !="0")
				this.excelExport.MyApp.get_Range( rngTemp.Cells[rowCount+12,3], rngTemp.Cells[rowCount+12,3]).Value2 ="Yes";
			else
				this.excelExport.MyApp.get_Range( rngTemp.Cells[rowCount+12,3], rngTemp.Cells[rowCount+12,3]).Value2 = "No";

			#region Catering Request
			rngTemp = this.excelExport.MyApp.get_Range("CateringRequest", Type.Missing );
			if (lgsInfo.IsNeedCatering)
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 ="Yes";
				this.excelExport.MyApp.get_Range( rngTemp.Cells[2,3], rngTemp.Cells[2,3]).Value2 =lgsInfo.CateringDescription;
			}
			else
				this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 ="No";
			#endregion				
		}

		private void GeneratePaymentInfo(BEGDC.CC.BL.Common.Query.IQuery queryService, int appId)
		{
			BEGDC.CC.Entity.PaymentInfo paymentInfo=queryService.GetPaymentInfo(appId);
			if (paymentInfo==null)
				return;
			BEGDC.CC.BL.Common.IUtility utility = AppContainer.Instance[ServiceConst.UtilityService] as BEGDC.CC.BL.Common.IUtility;
			IList list;

			Excel.Range rngTemp = this.excelExport.MyApp.get_Range("Payment", Type.Missing );
			this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = paymentInfo.DecryptedEstimatedCustomerTotalCost;
			this.excelExport.MyApp.get_Range( rngTemp.Cells[2,3], rngTemp.Cells[2,3]).Value2 = paymentInfo.DecryptedEstimateCustomerdIndividualCost;

			//Cost center info
			int rowCount=2;
			double totalCost = 0;
			DataTable dtCost=queryService.GetCostCenter(paymentInfo.Id);
			rngTemp = this.excelExport.MyApp.get_Range("CostCenter", Type.Missing );

			if (dtCost.Rows.Count>0)
			{				
				while (dtCost.Rows.Count > rowCount)
				{
					this.excelExport.MyApp.get_Range(rngTemp.Cells[2,1],rngTemp.Cells[2,7]).EntireRow.Copy(Type.Missing);
					this.excelExport.MyApp.get_Range(rngTemp.Cells[2,1],rngTemp.Cells[2,7]).EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);
					rowCount++;
				}

				rngTemp = this.excelExport.MyApp.get_Range("CostCenter", Type.Missing);
		
				for( int i=0; i < dtCost.Rows.Count; i++)
				{
					if( dtCost.Rows[i]["CostCenterCode"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,1], rngTemp.Cells[i+1,1]).Value2=dtCost.Rows[i]["CostCenterCode"];
					if( dtCost.Rows[i]["Cost"] != System.DBNull.Value )
					{
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,3], rngTemp.Cells[i+1,3]).Value2=dtCost.Rows[i]["Cost"].ToString();
						totalCost += Convert.ToDouble(dtCost.Rows[i]["Cost"].ToString());
					}
					if( dtCost.Rows[i]["ActualCost"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,6], rngTemp.Cells[i+1,6]).Value2=dtCost.Rows[i]["ActualCost"].ToString();
				}			
			}
			
			this.excelExport.MyApp.get_Range(rngTemp.Cells[rowCount+1,7], rngTemp.Cells[rowCount+1,7]).Value2 = totalCost;
			this.excelExport.MyApp.get_Range(rngTemp.Cells[rowCount+2,3], rngTemp.Cells[rowCount+2,3]).Value2 = paymentInfo.ExpenseType.DicItemKey;
			this.excelExport.MyApp.get_Range(rngTemp.Cells[rowCount+3,3], rngTemp.Cells[rowCount+3,3]).Value2 = paymentInfo.CustomerPerdiem;
			this.excelExport.MyApp.get_Range(rngTemp.Cells[rowCount+4,3], rngTemp.Cells[rowCount+4,3]).Value2 = paymentInfo.Comments;
		}

		#region ICCExcelExport Members

		public void Export(System.Web.UI.Page page, int id)
		{			
			//generate filename randomly
			GenerateFileName();

			this.GenerateEBDFormA( id );

			this.excelExport.Export2Client(page, this.fileName, "EBDFormA.xls");
		}

		#endregion
	}
}
